﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace DataAccess
{
    public  class MSSqlDataAccess:AbstractDataAccess
    {
        #region Constructor
		/// <summary>
		/// 默认构造函数
		/// </summary>
		public MSSqlDataAccess()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		/// <summary>
		/// 构造函数
		/// </summary>
		/// <param name="conn">MsSql数据连接对象</param>
		public MSSqlDataAccess(SqlConnection conn)
		{
			this.m_DbConnection=conn;
		}
		/// <summary>
		/// 构造函数
		/// </summary>
		/// <param name="connectionString">数据库连接字符串</param>
		public MSSqlDataAccess(string connectionString)
		{
			this.m_DbConnection=new SqlConnection(connectionString);
		}

		#endregion

		#region IDataAccess 成员

		#region Support Property & method
		/// <summary>
		/// 返回数据库类型枚举成员(DatabaseType)
		/// </summary>
		public override DatabaseType DatabaseType
		{
			get{return DatabaseType.MSSQLServer;}
		}
	
		private SqlConnection m_DbConnection;
		/// <summary>
		/// 返回数据库连接接口
		/// </summary>
		public override IDbConnection DbConnection
		{
			get
			{
				return m_DbConnection;
			}
		}
		
		private SqlTransaction trans=null;
		/// <summary>
		/// 开始一个事务
		/// </summary>
		/// <returns>数据库事务接口</returns>
		public override IDbTransaction BeginTransaction()
		{
			trans=m_DbConnection.BeginTransaction();
			return trans;
		}

		#endregion Support Property & method

		#region ExecuteNonQuery
		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="commandType">CommandType对象</param>
		/// <param name="commandText">要执行的SQL语句</param>
		/// <param name="commandParameters">SQL语句的参数集合</param>
		/// <param name="row">DataRow对象</param>
		/// <param name="sqlStruct">SqlStruct对象,封装Sql语句及参数</param>
		/// <returns>执行SQL语句影响的行数</returns>
		public override int ExecuteNonQuery(CommandType commandType, string commandText, QueryParameterCollection commandParameters ,DataRow row,SqlStruct sqlStruct)
		{
			SqlCommand cmd=new SqlCommand();
			if(sqlStruct == null)
				PrepareCommand(cmd,commandType, commandText,commandParameters);
			else
			{
				for(int i=0;i<sqlStruct.ParamFields.Length;i++)
				{
                    if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
                        commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
                    else
                        commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);	
				}
				PrepareCommand(cmd,CommandType.Text,sqlStruct.SqlString,commandParameters);
			}
			int tmpValue=cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return tmpValue;
		}
		#endregion ExecuteNonQuery

		#region ExecuteDataSet
		/// <summary>
		/// 执行SQL语句,返回DataSet对象.
		/// </summary>
		/// <param name="commandType">CommandType对象</param>
		/// <param name="commandText">要执行的SQL语句</param>
		/// <param name="commandParameters">SQL语句的参数集合</param>
		/// <param name="ds">DataSet对象</param>
		/// <param name="tableName">DataSet中表的名称</param>
		/// <returns>DataSet对象</returns>
		public override DataSet ExecuteDataset(CommandType commandType, string commandText, QueryParameterCollection commandParameters,DataSet ds,string tableName)
		{
			SqlCommand cmd=new SqlCommand();
			PrepareCommand(cmd,commandType, commandText,commandParameters);

			SqlDataAdapter da=new SqlDataAdapter(cmd);
			if(Object.Equals(tableName,null) || (tableName.Length<1))
				da.Fill(ds);
			else
				da.Fill(ds,tableName);
//			System.Windows.Forms.MessageBox.Show(tableName);
				
			cmd.Parameters.Clear();
			//Console.WriteLine(ds.Tables.Count.ToString());
			return ds;
		}

		public override DataSet ExecuteDataset(string commandText, QueryParameterCollection commandParameters,DataSet ds,int startRecord,int maxRecords,string tableName)
		{
			SqlCommand cmd=new SqlCommand();
			PrepareCommand(cmd,CommandType.Text, commandText,commandParameters);

			SqlDataAdapter da=new SqlDataAdapter(cmd);
			if(Object.Equals(tableName,null) || (tableName.Trim() == string.Empty))
				da.Fill(ds);
			else
				da.Fill(ds,startRecord,maxRecords,tableName);
			cmd.Parameters.Clear();
			return ds;
		}

		#endregion ExecuteDataSet

		#region ExecuteReader
		/// <summary>
		/// 执行SQL语句,返回IDataReader接口
		/// </summary>
		/// <param name="commandType">CommandType对象</param>
		/// <param name="commandText">要执行的SQL语句</param>
		/// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
		/// <returns>IDataReader</returns>
		public override IDataReader ExecuteReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
		{
			SqlCommand cmd=new SqlCommand();
			PrepareCommand(cmd,commandType, commandText,commandParameters);
			SqlDataReader dr=cmd.ExecuteReader();
			cmd.Parameters.Clear();
			return dr;
		}
		#endregion ExecuteReader	

		#region ExecuteScalar	
		/// <summary>
		/// 执行SQL语句,返回object接口
		/// </summary>
		/// <param name="commandType">CommandType对象</param>
		/// <param name="commandText">要执行的SQL语句</param>
		/// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
		/// <returns>object</returns>
		public override object ExecuteScalar(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
		{
			SqlCommand cmd=new SqlCommand();
			PrepareCommand(cmd,commandType, commandText,commandParameters);
			object tmpValue=cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return tmpValue;
		}
		#endregion ExecuteScalar	

		#region ExecuteXmlReader	
		/// <summary>
		/// 执行SQL语句,返回XmlReader对象
		/// </summary>
		/// <param name="commandType">CommandType对象</param>
		/// <param name="commandText">要执行的SQL语句</param>
		/// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
		/// <returns>XmlReader</returns>
		public override XmlReader ExecuteXmlReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
		{
			SqlCommand cmd=new SqlCommand();
			PrepareCommand(cmd,commandType, commandText,commandParameters);
			XmlReader reader=cmd.ExecuteXmlReader();
			cmd.Parameters.Clear();
			return reader;
		}
		#endregion ExecuteXmlReader

		#endregion
		/// <summary>
		/// 处理SqlCommand对象,设置参数等信息.
		/// </summary>
		/// <param name="cmd">SqlCommand对象</param>
		/// <param name="commandType">CommandType对象</param>
		/// <param name="commandText">要执行的SQL语句</param>
		/// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
		private void PrepareCommand(SqlCommand cmd,CommandType commandType, string commandText, QueryParameterCollection commandParameters)
		{
            commandText = commandText.Replace(":", "@");
			cmd.CommandType=commandType;
			cmd.CommandText=commandText;
			cmd.Connection=this.m_DbConnection;
			cmd.Transaction = trans;
			if((commandParameters!=null) && (commandParameters.Count>0) )
			{
				for(int i=0;i<commandParameters.Count;i++)
				{
					cmd.Parameters.AddWithValue(commandParameters[i].ParameterName,commandParameters[i].Value);
					cmd.Parameters[i].Direction=commandParameters[i].Direction;
				}
			}
		}
	}
    
}
